package com.lu.dao.impl;

import com.lu.dao.BaseDao;
import com.lu.dao.GoodsDao;
import com.lu.pojo.Goods;
import com.lu.pojo.Page;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class GoodsDaoImpl extends BaseDao implements GoodsDao {
    @Override
    public List<Goods> getAllGoods() {
        List<Goods> goodsList = new ArrayList<>();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select GId,GName,Price,RegisterDate from Goods";
        try {
            con = getCon();
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if (rs != null) {
                while (rs.next()) {
                    int gid = rs.getInt("GId");
                    String gname = rs.getString("GName");
                    Double price = rs.getDouble("Price");
                    Date registerdate = rs.getDate("RegisterDate");
                    Goods goods = new Goods(gid, gname, price, registerdate);
                    goodsList.add(goods);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(con, pstmt, rs);
        }
        return goodsList;
    }

    @Override
    public List<Goods> getSomeGoodsByUid(int uid) {
        List<Goods> goodsList = new ArrayList<>();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select GId,GName,Price,RegisterDate from Goods where UId = ?";
        try {
            con = getCon();
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, uid);
            rs = pstmt.executeQuery();
            if (rs != null) {
                while (rs.next()) {
                    int gid = rs.getInt("GId");
                    String gname = rs.getString("GName");
                    Double price = rs.getDouble("Price");
                    Date registerdate = rs.getDate("RegisterDate");
                    Goods goods = new Goods(gid, uid, gname, price, registerdate);
                    goodsList.add(goods);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(con, pstmt, rs);
        }
        return goodsList;
    }

    @Override
    public List<Goods> getSomeGoodsByPage(Page page) {
        List<Goods> goodsList = new ArrayList<>();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select GId,GName,Price,RegisterDate from Goods limit ?,?";
        try {
            con = getCon();
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, ((page.getCurrentPage() - 1) * page.getCount()));
            pstmt.setInt(2,page.getCount());
            rs = pstmt.executeQuery();
            if (rs != null) {
                while (rs.next()) {
                    int gid = rs.getInt("GId");
                    String gname = rs.getString("GName");
                    Double price = rs.getDouble("Price");
                    Date registerdate = rs.getDate("RegisterDate");
                    Goods goods = new Goods(gid, gname, price, registerdate);
                    goodsList.add(goods);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(con, pstmt, rs);
        }
        return goodsList;
    }

    @Override
    public int getCountGoods() {
        int result = 0;
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select count(1) from Goods";
        try {
            con = getCon();
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                result = rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return result;
    }

    @Override
    public Goods getOneGoodsById(int gid) {
        Goods goods = null;
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select GId,UId,GName,Price,RegisterDate,Sell from Goods where GId = ?";
        try {
            con = getCon();
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, gid);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                int uid = rs.getInt("UId");
                String gname = rs.getString("GName");
                Double price = rs.getDouble("Price");
                Date registerdate = rs.getDate("RegisterDate");
                String sell = rs.getString("Sell");
                goods = new Goods(gid, uid, gname, price, registerdate, sell);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(con, pstmt, rs);
        }
        return goods;
    }

    @Override
    public int addOneGoods(Goods goods) {
        String sql = "INSERT INTO Goods(TId,UId,GName,Price,RegisterDate,Description,CREATED_By,CREATED_Time) VALUES(?,?,?,?,NOW(),?,?,NOW())";
        Object[] parma = {goods.getTid(), goods.getUid(), goods.getGname(), goods.getPrice(), goods.getDescription(), goods.getCreatedBy()};
        return executeUpdate(sql, parma);
    }

    @Override
    public int updateOneGoods(Goods goods) {
        String sql = "UPDATE Goods SET TId=?,GName=?,Price=?,Description=?,UPDATED_By=?,UPDATED_Time=Now() WHERE GId = ?";
        Object[] parma = {goods.getTid(), goods.getGname(), goods.getPrice(), goods.getDescription(), goods.getUpdatedBy(), goods.getGid()};
        return executeUpdate(sql, parma);
    }

    @Override
    public int delOneGoods(int gid) {
        String sql = "DELETE FROM Goods WHERE GId = ?";
        Object[] parma = {gid};
        return executeUpdate(sql, parma);
    }

    @Override
    public int updateSell(Goods goods) {
        String sql = "Update Goods set Sell = ?,UPDATED_By=?,UPDATED_Time= NOW() where GId = ?";
        Object[] parma = {goods.getSell(),goods.getUpdatedBy(),goods.getGid()};
        return executeUpdate(sql,parma);
    }
}
